﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using OpenSoftStore.BusinessLogic.Money;
using OpenSoftStore.Orm;
using OpenSoftStore.Orm.Logic;
using OpenSoftStore.Orm.Model;
using OpenSoftStore.Orm.Static;
using OpenSoftStore.Orm.Status;
using OpenSoftStore.Orm.View;

namespace OpenSoftStore.BusinessLogic.Member
{
    public class MemberLogic : ABusinessLogic
    {
        #region Fields
        public MoneyLogic MoneyBll;
        #endregion

        public MemberLogic()
        {
            MoneyBll = new MoneyLogic();
        }

        #region Methods

        public Boolean ExitMemberByPass(T_Member member)
        {
            List<T_Member> members = Spa.Where<T_Member>(m => m.Id == member.Id && m.Pass == member.Pass).ToList();
            return members.Count >= 1;
        }

        public Boolean ClearPass(T_Member member)
        {
            string sql = string.Format("update T_Member set Pass='' where Id='{0}'", member.Id);
            return Spa.RunSqLcmd(sql);
        }

        public Boolean UpdatePass(T_Member member)
        {
            string sql = string.Format("update T_Member set Pass='{0}' where Id='{1}'", member.Pass, member.Id);
            return Spa.RunSqLcmd(sql);
        }

        public List<V_Member> GetMemberView(string strWhere = "", string strOrderBy = "")
        {
            string sql = "" +
                         "select " +
                         "  T_Member.*,T_Shop.Name as ShopName,T_Tree.Name as TreeName " +
                         "from " +
                         "  (T_Member inner join T_Shop on T_Member.ShopId=T_Shop.Id) " +
                         "  inner join T_Tree on T_Member.TreeId=T_Tree.Id ";
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            if (string.IsNullOrEmpty(strOrderBy)) strOrderBy = " Order By T_Member.AddTime desc ";
            var members = Spa.SelectWhere<V_Member>(sql + strOrderBy).ToList();
            return members;
        }

        public DataTable GetMemberDataTable(string strWhere = "", string strOrderBy = "")
        {
            string sql = "" +
                         "select " +
                         "  T_Member.*,T_Shop.Name as ShopName,T_Tree.Name as TreeName " +
                         "from " +
                         "  (T_Member left join T_Shop on T_Member.ShopId=T_Shop.Id) " +
                         "  left join T_Tree on T_Member.TreeId=T_Tree.Id ";
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            if (string.IsNullOrEmpty(strOrderBy)) strOrderBy = " Order By T_Member.AddTime desc ";
            var members = Spa.RunSqLcmdDataSet(sql + strOrderBy);
            if (members==null) return new DataTable();
            if (members.Tables.Count == 0) return new DataTable();
            return members.Tables[0];
        }

        public V_Member GetMemberViewObject(string strWhere)
        {
            string sql = "" +
                         "select " +
                         "  T_Member.*,T_Shop.Name as ShopName,T_Tree.Name as TreeName " +
                         "from " +
                         "  (T_Member inner join T_Shop on T_Member.ShopId=T_Shop.Id) " +
                         "  inner join T_Tree on T_Member.TreeId=T_Tree.Id ";
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            var data = Spa.SelectWhere<V_Member>(sql).ToList();
            if (!data.Any()) return null;
            return data[0];
        }

        public int Add(T_Member o)
        {
            o.State = MemberStatus.正常.ToString();
            o.ShopId = S_Admin.ShopId;
            return Spa.Add(o);
        }

        public int Save(T_Member member)
        {
            int count;
            if (string.IsNullOrEmpty(member.Id))
            {
                member.State = MemberStatus.正常.ToString();
                member.ShopId = S_Admin.ShopId;
                count = Spa.Add(member);
                Spa.SetCountNo("T_Member", member.CardId);
            }
            else
            {
                count = Spa.Update(member);
            }
            return count;
        }

        public List<V_MemberOwed> GetMemberOwedView(string strWhere = "")
        {
            string sql = "Select *,(CashPay + BankPay + ValuePay + AllReturn) as AllPay,(FavorMoney + ReturnFavor) as AllFavor,(NeedPay - CashPay - BankPay - ValuePay - FavorMoney - AllReturn - ReturnFavor) as LastOwed from (Select * from (Select CashPay,BankPay,ValuePay,'开单欠款' as Type,NeedPay,RealPay,FavorMoney,OwedMoney,MemberId,Id,BillNo as Descr,AddTime From [T_Bill] union Select CashPay,BankPay,ValuePay,'办理套餐欠款' as Type,NeedPay,RealPay,FavorMoney,OwedMoney,MemberId,Id,'' as Descr,AddTime From [T_DinnerSale] union Select CashPay,BankPay,0 as ValuePay,'充值欠款' as Type,NeedPay,RealPay,FavorMoney,OwedMoney,MemberId,Id,'' as Descr,AddTime From [T_MemberValueRecord] ) as t1 Left Join ( Select iif(isnull(Sum(ReturnMoney)),0,Sum(ReturnMoney)) as AllReturn,iif(isnull(Sum(FavorMoney)),0,Sum(FavorMoney)) as ReturnFavor,ObjectId  From [T_MemberReturnMoneyItem] Group by ObjectId ) as t2 on t1.Id = t2.ObjectId)  ";
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            return Spa.SelectWhere<V_MemberOwed>(sql).ToList();
        }

        public DataTable GetMemberOwedDataTable(string strWhere = "")
        {
            //string memberId, DateTime from, DateTime to, string isEquel, string shopId, string buzType
            //strWhere = "Where AddTime >= @From And AddTime <= @To ";
            string sql = "Select Type,NeedPay, CashPay + BankPay + ValuePay + iif(isnull(AllReturn),0,AllReturn) as AllPay, FavorMoney + iif(isnull(ReturnFavor),0,ReturnFavor) as AllFavor, NeedPay - CashPay - BankPay - ValuePay - FavorMoney -  iif(isnull(AllReturn),0,AllReturn) - iif(isnull(ReturnFavor),0,ReturnFavor) as LastOwed, Descr, AddTime, Name, CardId, CarNo from (  Select * from (  Select CashPay,BankPay,ValuePay,'开单欠款' as Type,NeedPay,RealPay,FavorMoney,OwedMoney,MemberId,Id,BillNo as Descr,AddTime From [T_Bill] union Select CashPay,BankPay,ValuePay,'办理套餐欠款' as Type,NeedPay,RealPay,FavorMoney,OwedMoney,MemberId,Id,'' as Descr,AddTime From [T_DinnerSale] union  Select CashPay,BankPay,0 as ValuePay,'充值欠款' as Type,NeedPay,RealPay,FavorMoney,OwedMoney,MemberId,Id,'' as Descr,AddTime From [T_MemberValueRecord] ) as t1  Left Join  ( Select iif(isnull(Sum(ReturnMoney)),0,Sum(ReturnMoney)) as AllReturn,iif(isnull(Sum(FavorMoney)),0,Sum(FavorMoney)) as ReturnFavor,ObjectId  From [T_MemberReturnMoneyItem] Group by ObjectId ) as t2  on t1.Id = t2.ObjectId  ) as t3 left join (Select Id,Name,CarNo,CardId from T_Member ) as t4  on t3.MemberId=t4.Id ";
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            var data=Spa.RunSqLcmdDataSet(sql);
            if (data==null) return new DataTable();
            if (data.Tables.Count==0) return new DataTable();
            return data.Tables[0];
        }

        public DataTable GetMemberSaleDataTable(DateTime dtFrom,DateTime dtTo,string strWhere="")
        {
            string sql = string.Format("Select Sum(t1.NeedPay) as Consume,  iif(isnull( Sum(t2.ConsumeBack)),0, Sum(t2.ConsumeBack)) as ConsumeBack,  Sum(t1.NeedPay)- iif(isnull( Sum(t2.ConsumeBack)),0, Sum(t2.ConsumeBack)) as Profit,  t3.CardId,  t3.Name,  t3.TelNum,  t3.CarNo  from   (  (Select * from [T_Bill] a where  a.State = '已结算' and a.PayTime >= #{0}# and a.PayTime <= #{1}# ) as t1  left join   ( select c.BillId ,sum(c.NeedPay) as ConsumeBack  from T_BillBack c Where c.PayTime >= #{0}# and c.PayTime <= #{1}# group by c.BillId) as t2  on t1.Id=t2.BillId  )  left join (Select * from  [T_Member] b ) as t3  on t1.MemberId = t3.Id  group by t1.MemberId,t3.CardId,t3.Name,t3.TelNum,t3.CarNo   order by Sum(t1.NeedPay)- iif(isnull( Sum(t2.ConsumeBack)),0, Sum(t2.ConsumeBack)) desc  ", dtFrom, dtTo);
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            var data = Spa.RunSqLcmdDataSet(sql);
            if (data == null) return new DataTable();
            if (data.Tables.Count == 0) return new DataTable();
            return data.Tables[0];
        }

        public DataTable GetMemberProductSaleDataTable(DateTime dtFrom,DateTime dtTo,string strWhere="")
        {
            string sql = string.Format("  Select   t1.ProductId,  t1.MemberId,  t1.MemberCardId,  t1.OwnCarNo,  t1.MemberName,  t1.ProductName,  t1.SaleAveragePrice,  t1.SaleSumarizeAmount,  t1.SaleSumarizePrice,  t2.BackAveragePrice,  t2.BackSumarizeAmount,  t2.BackSumarizePrice,  t1.SaleSumarizePrice - t2.BackSumarizePrice as Profit,  t1.IsProject  from  (  select   d.Id as ProductId,  a.Id as MemberId,   a.CardId as MemberCardID,  a.CarNo as OwnCarNo,  a.Name as MemberName,  d.Name +' '+ d.Specification + ' '+d.Model as ProductName,  e.SaleAveragePrice,  e.SaleSumarizeAmount,  e.SaleSumarizePrice,  d.IsProject               from T_Member a, T_Product d ,  (  select b.MemberId,c.ProductId,  sum(c.Price*c.Amount*c.Discount)/sum(c.Amount)  as SaleAveragePrice ,  sum(c.Amount) as SaleSumarizeAmount,  sum(c.Price*c.Amount*c.Discount) as SaleSumarizePrice  from T_Bill b,T_BillItem c  where b.Id=c.BillId  and b.State='已结算' and b.PayTime >= #{0}# and b.PayTime <= #{1}#   group by b.MemberId,c.ProductId  )e  where a.Id=e.MemberId and e.ProductId=d.Id  ) as  t1  left join (  Select g.MemberId,h.ProductId,  iif(isnull(sum(h.BackAmount * h.BackPrice )/sum(h.BackAmount)),0,sum(h.BackAmount * h.BackPrice )/sum(h.BackAmount)) as BackAveragePrice,  iif(isnull(sum(h.BackAmount)),0, sum(h.BackAmount)) as BackSumarizeAmount,  iif(isnull(sum(h.BackAmount * h.BackPrice )),0,sum(h.BackAmount * h.BackPrice )) as BackSumarizePrice  from T_BillBack g,T_BillBackItem h,T_BillItem i  where g.Id=h.BillBackId and h.BillItemId = i.Id and g.PayTime >= #{0}# and g.PayTime <= #{1}# group by g.MemberId,h.ProductId  ) as t2  on t1.ProductId = t2.ProductId and t1.MemberId =t2.MemberId    ", dtFrom, dtTo);
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            var data = Spa.RunSqLcmdDataSet(sql);
            if (data == null) return new DataTable();
            if (data.Tables.Count == 0) return new DataTable();
            return data.Tables[0];
        }

        public DataTable GetMemberProductSaleItemDataTable(DateTime dtFrom,DateTime dtTo,string productId, string memberId,string strWhere="")
        {
            string sql = string.Format(" SELECT  d.CardId AS MemberCardId,  d.CarNo AS OwnCarNo,  d.Name as MemberName, e.Name +' '+e.Specification+' '+ e.Model as ProductName, '销售' as Type, c.Price, c.Amount, c.Discount, c.Price*c.Amount*c.Discount  AS SumarizePrice, b.PayTime FROM T_Bill AS b, T_BillItem AS c, T_Member AS d, T_Product AS e WHERE  b.MemberId=d.Id AND c.ProductId=e.Id AND b.Id=c.BillId and b.PayTime >= #{0}# and b.PayTime <= #{1}# and e.Id='{2}' and d.Id='{3}' and b.State='已结算' Union SELECT    d.CardId AS MemberCardId,  d.CarNo AS OwnCarNo,  d.Name as MemberName, e.Name +' '+e.Specification+' '+ e.Model as ProductName, '退货' as Type, c.BackPrice as Price, c.BackAmount as Amount, '' as Discount, c.BackPrice * c.BackAmount   AS SumarizePrice, b.PayTime FROM T_BillBack AS b, T_BillBackItem AS c, T_Member AS d, T_Product AS e,T_BillItem f WHERE  b.MemberId=d.Id AND c.ProductId=e.Id AND b.Id=c.BillBackId and f.Id=c.BillItemId  and b.PayTime >= #{0}# and b.PayTime <= #{1}# and e.Id = '{2}' and d.Id = '{3}' ", dtFrom, dtTo, productId, memberId);
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            var data = Spa.RunSqLcmdDataSet(sql);
            if (data == null) return new DataTable();
            if (data.Tables.Count == 0) return new DataTable();
            return data.Tables[0];
        }

        public DataTable GetMemberActivityDataTable(DateTime dtFrom, DateTime dtTo, string strWhere = "", string strOrderBy = "")
        {
            string sql = string.Format(" Select distinct a.Id,a.CardId,a.Name,a.CarNo,a.State,a.Mark,a.BrithDate,a.Qq,a.Email,a.Fax,a.TelNum,  a.Address,a.Remark,a.OwedMoney, a.HaveValue , a.MemberDis,a.AddTime,  b.Name as TreeName,c.Name as ShopName , d.LastActiveBusiTime   from [T_Member] a,[T_Tree] b,[T_Shop] c ,  (  select  w.Id, iif(isnull(x.LastActiveBusiTime),#1800-08-08#,x.LastActiveBusiTime) as LastActiveBusiTime   from   T_Member w  left join   (  select l.MemberId ,max(l.AddTime) as LastActiveBusiTime  from T_Bill l  group by l.MemberId  )  x   on w.Id=x.MemberId   ) as d  where a.TreeId = b.Id  and d.Id=a.Id  and a.ShopId=c.Id  and (d.LastActiveBusiTime < #{0}# or d.LastActiveBusiTime>#{1}#)  ", dtFrom, dtTo);
            if (!string.IsNullOrEmpty(strWhere)) sql += strWhere;
            if (string.IsNullOrEmpty(strOrderBy)) strOrderBy = "  order by d.LastActiveBusiTime  DESC ";
            sql += strOrderBy;
            var data = Spa.RunSqLcmdDataSet(sql);
            if (data == null) return new DataTable();
            if (data.Tables.Count == 0) return new DataTable();
            return data.Tables[0];
        }

        public int ReturnMoney(T_MemberReturnMoney memberReturnMoney, List<T_MemberReturnMoneyItem> memberReturnMoneyItems)
        {
            if (string.IsNullOrEmpty(memberReturnMoney.Id))
            {
                memberReturnMoney.Id = Guid.NewGuid().ToString("D");
            }

            if (memberReturnMoney.CashPay > 0) memberReturnMoney.PayType = "现金";
            if (memberReturnMoney.BankPay > 0) memberReturnMoney.PayType = "银行转账/刷卡";
            foreach (var memberReturnMoneyItem in memberReturnMoneyItems)
            {
                memberReturnMoneyItem.ReturnId = memberReturnMoney.Id;
            }

            T_Member member = Spa.Where<T_Member>(m => m.Id == memberReturnMoney.MemberId).FirstOrDefault();
            member.OwedMoney -= memberReturnMoney.ReturnMoney;
            //member.HaveValue -=memberReturnMoney.
            Spa.UpdateObject(member);

            Spa.AddObject(memberReturnMoney);
            Spa.AddObjects(memberReturnMoneyItems);
            int row = Spa.SaveChanges();

            var lMoney = new L_Money
            {
                TreeId = "a20b74ae-bfa1-417b-af6f-247bcee86cb1",
                TreeName = "客户还款",
                Type = "收入",
                ObjectId = memberReturnMoney.Id,
                CardId = member.CardId,
                BankPayMoney = memberReturnMoney.BankPay,
                CashPayMoney = memberReturnMoney.CashPay,
                BankAccountId = memberReturnMoney.BankAccountId,
                AdminId = memberReturnMoney.AdminId,
                ShopId = memberReturnMoney.ShopId
            };
            row += MoneyBll.AddMoeny(lMoney);

            return row;
        }

        public List<T_MemberReturnMoney> GetMemberReturnMoneys(string where = "")
        {
            return Spa.Where<T_MemberReturnMoney>(where).ToList();
        }

        public List<T_MemberReturnMoneyItem> GetMemberReturnMoneyItems(string where = "")
        {
            return Spa.Where<T_MemberReturnMoneyItem>(where).ToList();
        }


        public int AddPreorder(T_Preorder preorder)
        {
            return Spa.Add(preorder);
        }

        public int AddRemind(T_Remind remind)
        {
            return Spa.Add(remind);
        }

        public DataTable GetPreorderTable(string whereStr = "")
        {
            string sql = " select P.Id, P.AddTime, P.Remark, M.Name, M.TelNum, M.CardId,A.Name as AdminName ";
            sql += " from (T_Preorder as P inner join T_Member as M on P.MemberId=M.Id) ";
            sql += " inner join T_Admin as A on P.AdminId=A.Id ";
            if (Spa.RunSqLcmdDataSet(sql + whereStr).Tables.Count == 0) {
                return new DataTable();
            }
            return Spa.RunSqLcmdDataSet(sql + whereStr).Tables[0];
        }

        public DataTable GetRemindView(string whereStr = "")
        {
            string sql = " select R.*, M.Name, M.CardId, M.TelNum  ";
            sql += " from (T_Remind as R inner join T_Member as M on R.MemberId=M.Id) ";
            sql += " inner join T_Tree as T on T.Id=R.TreeId  ";
            if (Spa.RunSqLcmdDataSet(sql + whereStr).Tables.Count == 0)
            {
                return new DataTable();
            }
            return Spa.RunSqLcmdDataSet(sql + whereStr).Tables[0];
        }

        public int SaveMemberTreeDinner(T_Tree tree, T_MemberTree memberTree, List<T_MemberTreeDinner> list)
        {
            bool add = true;
            if (string.IsNullOrEmpty(tree.Id))
            {
                tree.Id = Guid.NewGuid().ToString("D");
                memberTree.TreeId = tree.Id;
                tree.RootType = "MemberTree";
                var parentTree = Spa.Where<T_Tree>(m => m.Id == tree.ParentId).FirstOrDefault();
                var count = Spa.Where<T_Tree>(m => m.ParentId == tree.ParentId).Count();
                if (parentTree != null)
                {
                    tree.Code = parentTree.Code + count + ".";
                }
                memberTree.Id = Guid.NewGuid().ToString("D");
                Spa.AddObject(memberTree); 
            }
            else
            {
                memberTree.TreeId = tree.Id;
                if (string.IsNullOrEmpty(memberTree.Id))
                {
                    memberTree.Id = Guid.NewGuid().ToString("D");
                    Spa.AddObject(memberTree); 
                }else
                    Spa.UpdateObject(memberTree);
                var memberTreeDinnerDeletes = Spa.Where<T_MemberTreeDinner>(m => m.MemberTreeId == memberTree.Id);
                Spa.DeleteObjects(memberTreeDinnerDeletes);
                Spa.SaveChanges();
                add = false;
            }

            if (add)
                Spa.AddObject(tree);
            else
                Spa.UpdateObject(tree);

            foreach (var item in list)
            {
                item.Id = Guid.NewGuid().ToString("D");
                item.MemberTreeId = memberTree.Id;
            }

            Spa.AddObjects(list);
            int row = Spa.SaveChanges();
            return row;
        }

        public IEnumerable<T_MemberTreeDinner> GetMemberTreeDinnerView(string whereStr = "")
        {
            string sql = "Select T_MemberTreeDinner.Id, ProductId,ProductName from T_MemberTree   ";
            sql += " inner join  T_MemberTreeDinner on T_MemberTree.Id=T_MemberTreeDinner.MemberTreeId ";
            if (!string.IsNullOrEmpty(whereStr)) sql += whereStr;
            return Spa.SelectWhere<T_MemberTreeDinner>(sql).ToList();
        }

        public IEnumerable<T_MemberTree> GetMemberTreeView(string whereStr = "")
        {
            string sql = "Select T_MemberTree.Id,TreeId,Discount,Present from T_MemberTree   ";
            sql += " inner join  T_Tree on T_Tree.Id=T_MemberTree.TreeId  ";
            if (!string.IsNullOrEmpty(whereStr)) sql += whereStr;
            return Spa.SelectWhere<T_MemberTree>(sql).ToList();
        }

        public int DeleteMemberTree(List<T_MemberTree> memberTrees)
        {
            Spa.DeleteObjects(memberTrees);
            var ids = memberTrees.Select(m => "'" + m.Id + "'");
            string inStr = string.Join(",", ids);
            var MemberTreeDinners = Spa.Where<T_MemberTreeDinner>("  MemberTreeId in (" + inStr + ")") ?? new List<T_MemberTreeDinner>();
            Spa.DeleteObjects(MemberTreeDinners);
            return Spa.SaveChanges();
        }

        #endregion
    }
}

